package com.xzy.demo2;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;

/**
 * 向Excel导入数据
 *
 * @author xzy
 * @date 2021/12/17 10:57
 */
public class Main {
    public static void main(String[] args) throws IOException {
        merged();
    }

    /**
     * 生成Excel导入模板（简单）
     */
    public static void method1() throws IOException {
        // 1.创建Excel
        XSSFWorkbook workbook = new XSSFWorkbook();

        // 2.创建Sheet
        XSSFSheet sheet = workbook.createSheet("Sheet名称");

        // 3.创建标题行
        XSSFRow titleRow = sheet.createRow(0); // 0-based
        titleRow.createCell(0).setCellValue("字符串"); // 0-based
        titleRow.createCell(1).setCellValue("整数");
        titleRow.createCell(2).setCellValue("小数");
        titleRow.createCell(3).setCellValue("日期");
        titleRow.createCell(4).setCellValue("时间");
        titleRow.createCell(5).setCellValue("布尔");

        // 4.插入示例内容
        for (int i = 0; i < 3; i++) {
            int lastRowNum = sheet.getLastRowNum();
            XSSFRow exampleRow = sheet.createRow(lastRowNum + 1);
            exampleRow.createCell(0).setCellValue("Xxx");
            exampleRow.createCell(1).setCellValue(1);
            exampleRow.createCell(2).setCellValue(3.14);
            exampleRow.createCell(3).setCellValue("2021/12/17 10:57");
            exampleRow.createCell(4).setCellValue("10:57");
            exampleRow.createCell(5).setCellValue(true);
        }

        // 5.写出文件
        FileOutputStream outputStream = new FileOutputStream("src/main/resources/Xxx导入模板.xlsx");
        workbook.write(outputStream);
        workbook.close();

    }

    /**
     * 生成Excel导入模板（复杂）
     */
    public static void method2() throws IOException {
        // 1.创建Excel
        XSSFWorkbook workbook = new XSSFWorkbook();

        // 2.创建Sheet
        XSSFSheet sheet = workbook.createSheet("Sheet名称");


        // 3.创建标题行

        // 列格式
        // sheet.setDefaultColumnStyle();
        // sheet.setColumnWidth();

        // 标题行格式
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER); // 对齐方式
        titleStyle.setBorderBottom(BorderStyle.MEDIUM); // 边框线条
        titleStyle.setBorderTop(BorderStyle.MEDIUM);
        titleStyle.setBorderLeft(BorderStyle.MEDIUM);
        titleStyle.setBorderRight(BorderStyle.MEDIUM);
        titleStyle.setBottomBorderColor(IndexedColors.RED.getIndex()); // 边框颜色
        titleStyle.setTopBorderColor(IndexedColors.RED.getIndex());
        titleStyle.setLeftBorderColor(IndexedColors.RED.getIndex());
        titleStyle.setRightBorderColor(IndexedColors.RED.getIndex());
        titleStyle.setFillPattern(FillPatternType.FINE_DOTS); // 背景颜色
        titleStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
        titleStyle.setShrinkToFit(true); // 自动换行
        XSSFFont titleFont = workbook.createFont(); // 字体：颜色、加粗、下划线...
        titleFont.setFontName("宋体");
        titleFont.setColor(IndexedColors.BLUE.getIndex());
        titleFont.setBold(true);
        titleStyle.setFont(titleFont);
        titleStyle.setLocked(true); // 锁定
        titleStyle.setWrapText(true);

        XSSFRow titleRow = sheet.createRow(0);
        // titleRow.setHeight((short) 10); // 行高

        XSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellStyle(titleStyle);
        titleCell.setCellValue("字符串");

        titleCell = titleRow.createCell(1);
        titleCell.setCellStyle(titleStyle);
        titleCell.setCellValue("整数");

        titleCell = titleRow.createCell(2);
        titleCell.setCellStyle(titleStyle);
        titleCell.setCellValue("小数");

        titleCell = titleRow.createCell(3);
        titleCell.setCellStyle(titleStyle);
        titleCell.setCellValue("日期");

        titleCell = titleRow.createCell(4);
        titleCell.setCellStyle(titleStyle);
        titleCell.setCellValue("时间");

        titleCell = titleRow.createCell(5);
        titleCell.setCellStyle(titleStyle);
        titleCell.setCellValue("布尔");

        // 4.插入示例内容
        for (int i = 0; i < 3; i++) {
            int lastRowNum = sheet.getLastRowNum();
            XSSFRow exampleRow = sheet.createRow(lastRowNum + 1);
            exampleRow.createCell(0).setCellValue("Xxx");
            exampleRow.createCell(1).setCellValue(1);
            exampleRow.createCell(2).setCellValue(3.14);
            exampleRow.createCell(3).setCellValue("2021/12/17 10:57");
            exampleRow.createCell(4).setCellValue("10:57");
            exampleRow.createCell(5).setCellValue(true);
        }

        // 5.写出文件
        FileOutputStream outputStream = new FileOutputStream("src/main/resources/Xxx导入模板.xlsx");
        workbook.write(outputStream);
        workbook.close();

    }

    /**
     * 生成Excel导入模板（下拉框）
     */
    public static void method3() throws IOException {
        // 1.创建Excel
        XSSFWorkbook workbook = new XSSFWorkbook();

        // 2.创建Sheet
        XSSFSheet sheet = workbook.createSheet("Sheet名称");

        // 3.创建标题行
        XSSFRow titleRow = sheet.createRow(0); // 0-based
        titleRow.createCell(0).setCellValue("字符串"); // 0-based
        titleRow.createCell(1).setCellValue("整数");
        titleRow.createCell(2).setCellValue("小数");
        titleRow.createCell(3).setCellValue("日期");
        titleRow.createCell(4).setCellValue("时间");
        titleRow.createCell(5).setCellValue("布尔");

        // 4.下拉框
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(new String[]{"Aaa", "Bbb", "Ccc"}); // 下拉框选项
        CellRangeAddressList addressList = new CellRangeAddressList(1, 100, 0, 5); // 设置下拉框的单元格
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList); // 创建 DataValidation 对象
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);

        // 5.写出文件
        FileOutputStream outputStream = new FileOutputStream("src/main/resources/Xxx导入模板.xlsx");
        workbook.write(outputStream);
        workbook.close();
    }

    /**
     * 合并单元格
     *
     * @throws IOException -
     */
    public static void merged() throws IOException {
        StringBuilder tipContext = new StringBuilder("注意：");
        tipContext.append("\n").append("1.xxxxxxxxxxxxxxxxxx");
        tipContext.append("\n").append("2.xxxxxxxxxxxxxxxxxx");
        tipContext.append("\n").append("3.xxxxxxxxxxxxxxxxxx");
        short tipCount = 3;

        // 1.创建Excel
        XSSFWorkbook workbook = new XSSFWorkbook();

        // 2.创建Sheet
        XSSFSheet sheet = workbook.createSheet("Sheet名称");

        // 3.合并单元格
        short firstRow = 0;
        short lastRow = 0;
        short firstCol = 0;
        short lastCol = 5;
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));

        // 4.向合并的单元格写入内容
        XSSFRow row = sheet.createRow(firstRow);
        XSSFCell cell = row.createCell(firstCol);
        cell.setCellValue(tipContext.toString());

        // 调整行高
        short oneColumnHeight = row.getHeight();
        row.setHeight((short) (oneColumnHeight * tipCount));

        // 自动换行
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setShrinkToFit(true);
        cellStyle.setWrapText(true);
        cell.setCellStyle(cellStyle);

        // 5.写出文件
        FileOutputStream outputStream = new FileOutputStream("src/main/resources/Xxx导入模板.xlsx");
        workbook.write(outputStream);
        workbook.close();
    }

/*    public static void mvc(HttpServletResponse httpResponse){
        // 1.创建Excel
        XSSFWorkbook workbook = new XSSFWorkbook();

        // 2.创建Sheet
        XSSFSheet sheet = workbook.createSheet("Sheet名称");

        // 3.创建标题行
        XSSFRow titleRow = sheet.createRow(0);
        titleRow.createCell(0).setCellValue("字符串");
        titleRow.createCell(1).setCellValue("整数");
        titleRow.createCell(2).setCellValue("小数");
        titleRow.createCell(3).setCellValue("日期");
        titleRow.createCell(4).setCellValue("时间");
        titleRow.createCell(5).setCellValue("布尔");

        // 4.插入示例内容
        for (int i = 0; i < 3; i++) {
            int lastRowNum = sheet.getLastRowNum();
            XSSFRow exampleRow = sheet.createRow(lastRowNum + 1);
            exampleRow.createCell(0).setCellValue("Xxx");
            exampleRow.createCell(1).setCellValue(1);
            exampleRow.createCell(2).setCellValue(3.14);
            exampleRow.createCell(3).setCellValue("2021/12/17 10:57");
            exampleRow.createCell(4).setCellValue("10:57");
            exampleRow.createCell(5).setCellValue(true);
        }

        // 5.写出文件
        String fileName = "Xxx导入模板.xlsx";
        fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
        httpResponse.setHeader("Content-Disposition", "attachment; filename=" + fileName);
        try {
            OutputStream outputStream = new BufferedOutputStream(httpResponse.getOutputStream());
            workbook.write(outputStream);
            workbook.close();
        } catch (IOException e) {
            throw new BusinessException("");
        }
    }*/
}
